package com.qdu.servlet;

import com.qdu.entity.User;
import com.qdu.util.DatabaseUtil;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * 点击“查询1”按钮请求该Servlet，根据班级名称查询学生列表，并跳转到student_list.jsp页面，显示结果
 *
 * @author Anna
 */
@WebServlet("/a_sbls")
public class Admin_SearchBatchListServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        //1. 获取请求参数
        String batchName = req.getParameter("batchName");

        //2. 执行业务逻辑 
//        String driver_class = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//        String url = "jdbc:sqlserver://localhost:1433;databaseName=Crp";
//        String username = "sa";
//        String password = "sa";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();

        try {
//            Class.forName(driver_class);
//            con = DriverManager.getConnection(url, username, password);
            con = DatabaseUtil.getConnection();
            if (batchName.equals("User")) {
                ps = con.prepareStatement("select * from [dbo].[User] ");
                // ps.setString(1, batchName);
                rs = ps.executeQuery();

                while (rs.next()) {
                    list.add(new User(rs.getInt(1), rs.getString(2), rs.getString(3),
                             rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9)));
                }
            } else {
                ps = con.prepareStatement("select * from [dbo].[Admin] ");
                // ps.setString(1, batchName);
                rs = ps.executeQuery();

                while (rs.next()) {
                    list.add(new User(rs.getInt(1), rs.getString(2), rs.getString(3)));
                }
            }

            // ps.setString(1, batchName);
//            rs = ps.executeQuery();
//   
//            while (rs.next()) {
//                list.add(new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
//            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {

                if (null != rs) {
                    rs.close();
                }
                if (null != ps) {
                    ps.close();
                }
                if (null != con) {
                    con.close();
                }

            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        //3. 生成动态响应
        //为了能够将查到的学生列表和班级名称显示在student_list.jsp页面，可以将两个信息作为属性添加到请求对象中，并转发请求，从而将数据共享给student_list1.jsp页面
        req.setAttribute("studentList", list);
        req.setAttribute("batchName", batchName);
        req.getRequestDispatcher("/UserAndAdmin_list.jsp").forward(req, resp);
    }
}
